package tt.dz.biz.dao.user;

import java.util.Date;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.BusinessException;
import tt.dz.common.DzConstants;
import tt.dz.common.DzConstants.userLock;
import tt.dz.common.DzConstants.userType;
import tt.dz.common.LogUtil;
import tt.dz.vo.MerchantVO;
import tt.dz.vo.OnlineAccountVO;
import tt.dz.vo.Operator;
import tt.dz.vo.UserBaseVO;
import tt.dz.vo.UserDetailVO;
import tt.dz.vo.UserRelationshipVO;

@Repository("userDAO")
public class UserDAOImpl
  extends BaseDAO
{
  public static final String QUERY_USERS_BY_MOBILE = "select sop.name as userName, sop.password from pub_oper_list sop where   sop.mobile_no=:mobileNo";
  public static final String INSERT_OPER_LIST = "insert into pub_oper_list(manager_id,name,mobile_no,password) values (:userName,:userName,:userName,:password)";
  public static final String INSERT_USER_BASE = "insert into ms_user_base(user_id,mobile_no,regist_time,email,name,gender,id_type,id_no,user_type,quality,merchant_code,quota,allow_over_quota,login_times,is_online,password,salt,is_locked,locked_time,failure_times,is_valid,notify_url,enterprise_code)   values(:userId,:mobileNo,:registTime,:email,:name,:gender,:idType,:idNo,:userType,:quality,:merchantCode,:quota,:allowOverQuota,:loginTimes,:isOnline,:password,:salt,:isLocked,:lockedTime,:failureTimes,:isValid,:notifyUrl,:enterpriseCode )";
  public static final String INSERT_USER_DETAIL = "insert into MS_user_detail(user_id,nick_name,head_url,signature,district_code,add_detail,eletype,longitude,latitude,position_updated_time)  values(:userId,:nickName,:headUrl,:signature,:districtCode,:addDetail,:eletype,:longitude,:latitude,:positionUpdatedTime )";
  public static final String QUERY_USER_SEQUENCE = " select  nextval('user_serial') ";
  public static final String UPDATE_MS_USER_DETAIL = "update ms_user_detail  set nick_name=:nickName,head_url=:headUrl,signature=:signature,district_code=:districtCode,add_detail=:addDetail,eletype=:eletype,longitude=:longitude,latitude=:latitude,position_updated_time=:positionUpdatedTime where user_id=:userId";
  public static final String UPDATE_MS_USER_BASE = "update ms_user_base  set mobile_no=:mobileNo,regist_time=:registTime,email=:email,name=:name,gender=:gender,id_type=:idType,id_no=:idNo,user_type=:userType,quality=:quality,merchant_code=:merchantCode,quota=:quota,allow_over_quota=:allowOverQuota,login_times=:loginTimes,is_online=:isOnline,password=:password,salt=:salt,is_locked=:isLocked,locked_time=:lockedTime,failure_times=:failureTimes,is_valid=:isValid where user_id=:userId ";
  public static final String QUERY_MS_USER_BASE = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.password,t.salt,t.is_locked,t.locked_time,t.failure_times,t.is_valid,t.notify_url,t.enterprise_code  from ms_user_base as t where t.is_valid=1  ";
  public static final String QUERY_MS_USER_BASE_UNPWD = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.is_locked,t.locked_time,t.failure_times,t.notify_url,t.enterprise_code  from ms_user_base as t where t.is_valid=1  ";
  public static final String QUERY_MS_USER_DETAIL = "select  t.user_id,t.nick_name,t.head_url,t.signature,t.district_code,t.add_detail,t.eletype,t.longitude,t.latitude,t.position_updated_time  from ms_user_detail as t  where t.user_id=:userId ";
  private static final String QUERY_ENTERPRISE_USER = "select  a.user_id,a.name,a.quota,a.allow_over_quota,a.merchant_code ,a.enterprise_code from ms_user_base as a where a.is_valid  = 1 and a.user_type = 1";
  private static final String UPDATE_ENTERPRISE_USER = "update ms_user_base  set mobile_no=:mobileNo,regist_time=:registTime,email=:email,name=:name,gender=:gender,id_type=:idType,id_no=:idNo,user_type=:userType,quality=:quality,merchant_code=:merchantCode,quota=:quota,allow_over_quota=:allowOverQuota,login_times=:loginTimes,is_online=:isOnline,password=:password,is_locked=:isLocked,locked_time=:lockedTime, failure_times=:failureTimes,is_valid=:isValid,notify_url=:notifyUrl,enterprise_code=:enterpriseCode  where user_id=:userId  and user_type=1";
  private static final String QUERY_USER_BY_MOBILE = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid  from ms_user_base as t where t.mobile_no like :mobileNo and t.is_valid = 1";
  private static final String QUERY_SINGLE_USER_BY_MOBILE = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid  from ms_user_base as t where t.mobile_no = :mobileNo and t.is_valid = 1 ";
  private static final String QUERY_SINGLE_USER = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid ,t.enterprise_code from ms_user_base as t where t.user_id = :userId";
  private static final String QUERY_MERCHANT_USER = "select b.* from ms_user_base b  LEFT JOIN ms_merchant a on b.merchant_code = a.merchant_code where a.merchant_code = :merchantCode and a.is_valid = 1";
  private static final String INSERT_USERRELATIONSHIP = "insert into ms_user_relationship(user_id,relate_user_id,relate_time,sys_code,is_valid)  values(:userId,:relateUserId,:relateTime,:sysCode,:isValid )";
  private static final String LIST_ALL_USER_RELATIONSHIP = "select  a.user_id,a.relate_user_id,a.relate_time,a.sys_code,a.is_valid  from ms_user_relationship as a ";
  public static final String QUERY_USER_BASE = "select  ub.user_id,ub.mobile_no,ub.regist_time,ub.email,ub.name,ub.gender,ub.id_type,ub.id_no,ub.user_type,ub.notify_url,ub.quality,ub.merchant_code,ub.quota,ub.allow_over_quota,ub.login_times,ub.is_online,ub.salt,ub.is_locked,ub.locked_time,ub.failure_times,ub.is_valid,ub.balance  from ms_user_base  ub ";
  private static final String LIST_ALL_USERBASE = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid ,t.enterprise_code from ms_user_base as t ";
  
  public Operator queryUserByNameAndPwd(Operator user)
  {
    return (Operator)queryForObject("select sop.name as userName, sop.password from pub_oper_list sop where   sop.mobile_no=:mobileNo", user, Operator.class);
  }
  
  public int insertUser(Operator user)
  {
    return save("insert into pub_oper_list(manager_id,name,mobile_no,password) values (:userName,:userName,:userName,:password)", user);
  }
  
  public int insertUserByBase(UserBaseVO userBase)
  {
    return save("insert into ms_user_base(user_id,mobile_no,regist_time,email,name,gender,id_type,id_no,user_type,quality,merchant_code,quota,allow_over_quota,login_times,is_online,password,salt,is_locked,locked_time,failure_times,is_valid,notify_url,enterprise_code)   values(:userId,:mobileNo,:registTime,:email,:name,:gender,:idType,:idNo,:userType,:quality,:merchantCode,:quota,:allowOverQuota,:loginTimes,:isOnline,:password,:salt,:isLocked,:lockedTime,:failureTimes,:isValid,:notifyUrl,:enterpriseCode )", userBase);
  }
  
  public int insertUserByDetail(UserDetailVO userDetail)
  {
    return save("insert into MS_user_detail(user_id,nick_name,head_url,signature,district_code,add_detail,eletype,longitude,latitude,position_updated_time)  values(:userId,:nickName,:headUrl,:signature,:districtCode,:addDetail,:eletype,:longitude,:latitude,:positionUpdatedTime )", userDetail);
  }
  
  public int updateUserByBase(UserBaseVO userBase)
  {
    return save("update ms_user_base  set mobile_no=:mobileNo,regist_time=:registTime,email=:email,name=:name,gender=:gender,id_type=:idType,id_no=:idNo,user_type=:userType,quality=:quality,merchant_code=:merchantCode,quota=:quota,allow_over_quota=:allowOverQuota,login_times=:loginTimes,is_online=:isOnline,password=:password,salt=:salt,is_locked=:isLocked,locked_time=:lockedTime,failure_times=:failureTimes,is_valid=:isValid where user_id=:userId ", userBase);
  }
  
  public int updateUserByDetail(UserDetailVO userDetail)
  {
    return save("update ms_user_detail  set nick_name=:nickName,head_url=:headUrl,signature=:signature,district_code=:districtCode,add_detail=:addDetail,eletype=:eletype,longitude=:longitude,latitude=:latitude,position_updated_time=:positionUpdatedTime where user_id=:userId", userDetail);
  }
  
  public UserBaseVO getUserById(String userId)
  {
    try
    {
      StringBuilder sql = new StringBuilder("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.password,t.salt,t.is_locked,t.locked_time,t.failure_times,t.is_valid,t.notify_url,t.enterprise_code  from ms_user_base as t where t.is_valid=1  ");
      UserBaseVO param = new UserBaseVO();
      if ((userId != null) && (!"".equals(userId)))
      {
        sql.append(" and  t.user_id=:userId   ");
        param.setUserId(userId);
      }
      return (UserBaseVO)queryForObject(sql.toString(), param, UserBaseVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      return null;
    }
    catch (IncorrectResultSizeDataAccessException e)
    {
      throw new BusinessException("通过userId查询出多条用户信息，userId为：" + userId);
    }
  }
  
  public UserBaseVO getUserBaseByUserId(String userId, String mobileNo, String aCode)
  {
    try
    {
      StringBuffer sql = new StringBuffer("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.password,t.salt,t.is_locked,t.locked_time,t.failure_times,t.is_valid,t.notify_url,t.enterprise_code  from ms_user_base as t where t.is_valid=1  ");
      UserBaseVO param = new UserBaseVO();
      if ((userId != null) && (!"".equals(userId)))
      {
        sql.append(" and  t.user_id=:userId  and t.merchant_code=:merchantCode ");
        param.setUserId(userId);
        param.setMerchantCode(aCode);
      }
      if ((mobileNo != null) && (!"".equals(mobileNo)))
      {
        sql.append(" and  t.mobile_no=:mobileNo and t.merchant_code=:merchantCode ");
        param.setMobileNo(mobileNo);
        param.setMerchantCode(aCode);
      }
      return (UserBaseVO)queryForObject(sql.toString(), param, UserBaseVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      LogUtil.error("通过手机或userId查询用户为空，userId为：" + userId + ",手机号码为：" + mobileNo + ",运营商编码为：" + aCode);
      return null;
    }
    catch (IncorrectResultSizeDataAccessException e)
    {
      LogUtil.error("通过手机或userId查询出多条用户信息，userId为：" + userId + ",手机号码为：" + mobileNo + ",运营商编码为：" + aCode);
      throw e;
    }
  }
  
  public UserDetailVO getUserDetailByUserId(String userId)
  {
    UserDetailVO param = new UserDetailVO();
    param.setUserId(userId);
    return (UserDetailVO)queryForObject("select  t.user_id,t.nick_name,t.head_url,t.signature,t.district_code,t.add_detail,t.eletype,t.longitude,t.latitude,t.position_updated_time  from ms_user_detail as t  where t.user_id=:userId ", param, UserDetailVO.class);
  }
  
  public synchronized String queryUserSequence()
  {
    Long userSequence = querySequence("user_serial");
    return userSequence != null ? userSequence.toString() : null;
  }
  
  public List<UserBaseVO> queryCorpUsers(UserBaseVO param)
  {
    StringBuffer sql = new StringBuffer("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.password,t.salt,t.is_locked,t.locked_time,t.failure_times,t.is_valid,t.notify_url,t.enterprise_code  from ms_user_base as t where t.is_valid=1  ");
    if (null == param) {
      param = new UserBaseVO();
    }
    param.setUserType(DzConstants.userType.ENTERPRISE_CUSTOMER.value());
    sql.append(" and t.user_type=:userType ");
    if (StringUtils.isNotBlank(param.getMobileNo())) {
      sql.append(" and t.mobile_no=:mobileNo ");
    }
    if (StringUtils.isNotBlank(param.getEmail())) {
      sql.append(" and t.email=:email ");
    }
    sql.append(" order by regist_time desc ");
    return queryForList(sql.toString(), param, UserBaseVO.class);
  }
  
  public List<UserBaseVO> queryUserByUserType(Integer userType)
  {
    StringBuffer sql = new StringBuffer("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.is_locked,t.locked_time,t.failure_times,t.notify_url,t.enterprise_code  from ms_user_base as t where t.is_valid=1  ");
    UserBaseVO param = new UserBaseVO();
    if (userType != null)
    {
      param.setUserType(userType);
      sql.append(" and t.user_type=:userType ");
    }
    sql.append(" order by regist_time desc ");
    return queryForList(sql.toString(), param, UserBaseVO.class);
  }
  
  public List<UserBaseVO> queryEnterpriseUser()
  {
    return queryForList("select  a.user_id,a.name,a.quota,a.allow_over_quota,a.merchant_code ,a.enterprise_code from ms_user_base as a where a.is_valid  = 1 and a.user_type = 1", null, UserBaseVO.class);
  }
  
  public int updateEnterpriseUser(UserBaseVO userBaseVO)
  {
    return save("update ms_user_base  set mobile_no=:mobileNo,regist_time=:registTime,email=:email,name=:name,gender=:gender,id_type=:idType,id_no=:idNo,user_type=:userType,quality=:quality,merchant_code=:merchantCode,quota=:quota,allow_over_quota=:allowOverQuota,login_times=:loginTimes,is_online=:isOnline,password=:password,is_locked=:isLocked,locked_time=:lockedTime, failure_times=:failureTimes,is_valid=:isValid,notify_url=:notifyUrl,enterprise_code=:enterpriseCode  where user_id=:userId  and user_type=1", userBaseVO);
  }
  
  public List<UserBaseVO> queryUserBaseList()
  {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    return queryForList("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.password,t.salt,t.is_locked,t.locked_time,t.failure_times,t.is_valid,t.notify_url,t.enterprise_code  from ms_user_base as t where t.is_valid=1  ", parameters, UserBaseVO.class);
  }
  
  public int updateUserLockByUserId(String userId, DzConstants.userLock userlock)
  {
    UserBaseVO userBaseVo = getUserById(userId);
    userBaseVo.setIsLocked(userlock.value());
    switch (userlock.value().intValue())
    {
    case 0: 
      userBaseVo.setLockedTime(null);
      break;
    case 1: 
      userBaseVo.setLockedTime(new Date());
    }
    return updateUserByBase(userBaseVo);
  }
  
  public List<UserBaseVO> queryUserBase(UserBaseVO userBaseVO)
  {
    StringBuilder sql = new StringBuilder("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid ,t.enterprise_code from ms_user_base as t ");
    sql.append("  where 1 = 1 and is_valid = 1 ").append(assemblyConditions(userBaseVO, "t", true))
      .append("  order by  regist_time  desc ");
    userBaseVO = (UserBaseVO)convertObjectForLike(userBaseVO);
    return queryForList(sql.toString(), userBaseVO, UserBaseVO.class);
  }
  
  public UserBaseVO querySingleUserByMobile(UserBaseVO userBaseVO)
  {
    List<UserBaseVO> userList = queryForList("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid  from ms_user_base as t where t.mobile_no = :mobileNo and t.is_valid = 1 ", userBaseVO, UserBaseVO.class);
    if ((userList != null) && (!userList.isEmpty())) {
      return (UserBaseVO)userList.get(0);
    }
    return null;
  }
  
  public List<UserBaseVO> queryUserByMerchant(MerchantVO merchantVO)
  {
    return queryForList("select b.* from ms_user_base b  LEFT JOIN ms_merchant a on b.merchant_code = a.merchant_code where a.merchant_code = :merchantCode and a.is_valid = 1", merchantVO, UserBaseVO.class);
  }
  
  public int[] batchUpdateUser(List<UserBaseVO> userList)
  {
    return batchSave("update ms_user_base  set mobile_no=:mobileNo,regist_time=:registTime,email=:email,name=:name,gender=:gender,id_type=:idType,id_no=:idNo,user_type=:userType,quality=:quality,merchant_code=:merchantCode,quota=:quota,allow_over_quota=:allowOverQuota,login_times=:loginTimes,is_online=:isOnline,password=:password,salt=:salt,is_locked=:isLocked,locked_time=:lockedTime,failure_times=:failureTimes,is_valid=:isValid where user_id=:userId ", userList);
  }
  
  public UserBaseVO querySingleUser(UserBaseVO userBaseVO)
  {
    List<UserBaseVO> userList = queryForList("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid ,t.enterprise_code from ms_user_base as t where t.user_id = :userId", userBaseVO, UserBaseVO.class);
    if ((userList != null) && (!userList.isEmpty())) {
      return (UserBaseVO)userList.get(0);
    }
    return null;
  }
  
  public int insertUserRelationship(UserRelationshipVO userRelationshipVO)
  {
    return save("insert into ms_user_relationship(user_id,relate_user_id,relate_time,sys_code,is_valid)  values(:userId,:relateUserId,:relateTime,:sysCode,:isValid )", userRelationshipVO);
  }
  
  public List<UserRelationshipVO> queryUserRelationship(UserRelationshipVO userRelationshipVO)
  {
    String sql = "select  a.user_id,a.relate_user_id,a.relate_time,a.sys_code,a.is_valid  from ms_user_relationship as a  where 1 = 1 " + assemblyConditions(userRelationshipVO, "a", false);
    return queryForList(sql, userRelationshipVO, UserRelationshipVO.class);
  }
  
  public UserBaseVO queryUserDetailByVirtualCard(String cardNo)
    throws Exception
  {
    try
    {
      OnlineAccountVO oa = new OnlineAccountVO();
      oa.setCardNo(cardNo);
      StringBuilder sql = new StringBuilder("select  ub.user_id,ub.mobile_no,ub.regist_time,ub.email,ub.name,ub.gender,ub.id_type,ub.id_no,ub.user_type,ub.notify_url,ub.quality,ub.merchant_code,ub.quota,ub.allow_over_quota,ub.login_times,ub.is_online,ub.salt,ub.is_locked,ub.locked_time,ub.failure_times,ub.is_valid,ub.balance  from ms_user_base  ub ");
      sql.append(" ,ms_online_account oa where ub.user_id = oa.user_id and oa.card_no=:cardNo ");
      return (UserBaseVO)queryForObject(sql.toString(), oa, UserBaseVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      return null;
    }
    catch (Exception e)
    {
      LogUtil.error("通过虚拟卡号查询用户基本信息异常，参数cardNo为：" + cardNo, e);
      throw e;
    }
  }
}
